SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;				
	l_func_id			VARCHAR2(100):='CAPFNDREC_PROFILE';

BEGIN
	SELECT COUNT(1)
	INTO l_total_extracted   
	FROM
	(
	SELECT
		R.Responsibility_Key,
		'PER_BUSINESS_GROUP_ID'		Opt_Type,
		B.Name						Name,
		V.*
	FROM
		FND_RESPONSIBILITY_VL		R,
		FND_PROFILE_OPTIONS			O,
		FND_PROFILE_OPTION_VALUES	V,
		PER_BUSINESS_GROUPS			B
	WHERE	O.Profile_Option_Name				=	'PER_BUSINESS_GROUP_ID'
	AND		O.Application_ID					=	V.Application_ID
	AND		O.Profile_Option_ID					=	V.Profile_Option_ID
	AND		10003								=	V.Level_ID
	AND		R.Responsibility_ID					=	V.Level_Value
	AND		SUBSTR(R.Responsibility_Key,1,4)	=	UPPER('&1')
	AND		V.Profile_Option_Value				=	B.Business_Group_ID
	UNION
	SELECT
		R.Responsibility_Key,
		'PER_SECURITY_PROFILE_ID'	Opt_Type,
		B.Security_Profile_Name		Name,
		V.*
	FROM
		FND_RESPONSIBILITY_VL		R,
		FND_PROFILE_OPTIONS			O,
		FND_PROFILE_OPTION_VALUES	V,
		PER_SECURITY_PROFILES		B
	WHERE	O.Profile_Option_Name				=	'PER_SECURITY_PROFILE_ID'
	AND		O.Application_ID					=	V.Application_ID
	AND		O.Profile_Option_ID					=	V.Profile_Option_ID
	AND		10003								=	V.Level_ID
	AND		R.Responsibility_ID					=	V.Level_Value
	AND		SUBSTR(R.Responsibility_Key,1,4)	=	UPPER('&1')
	AND		V.Profile_Option_Value				=	B.Security_Profile_ID
	UNION
	SELECT
		R.Responsibility_Key,
		'ORG_ID'					Opt_Type,
		B.Name						Name,
		V.*
	FROM
		FND_RESPONSIBILITY_VL		R,
		FND_PROFILE_OPTIONS			O,
		FND_PROFILE_OPTION_VALUES	V,
		HR_ALL_ORGANIZATION_UNITS	B
	WHERE	O.Profile_Option_Name				=	'ORG_ID'
	AND		O.Application_ID					=	V.Application_ID
	AND		O.Profile_Option_ID					=	V.Profile_Option_ID
	AND		10003								=	V.Level_ID
	AND		R.Responsibility_ID					=	V.Level_Value
	AND		SUBSTR(R.Responsibility_Key,1,4)	=	UPPER('&1')
	AND		V.Profile_Option_Value				=	B.Organization_ID
	UNION
	SELECT
		R.Responsibility_Key,
		'GL_SET_OF_BKS_NAME'		Opt_Type,
		B.Short_Name				Name,
		V.*
	FROM
		FND_RESPONSIBILITY_VL		R,
		FND_PROFILE_OPTIONS			O,
		FND_PROFILE_OPTION_VALUES	V,
		GL_SETS_OF_BOOKS			B
	WHERE	O.Profile_Option_Name				=	'GL_SET_OF_BKS_NAME'
	AND		O.Application_ID					=	V.Application_ID
	AND		O.Profile_Option_ID					=	V.Profile_Option_ID
	AND		10003								=	V.Level_ID
	AND		R.Responsibility_ID					=	V.Level_Value
	AND		SUBSTR(R.Responsibility_Key,1,4)	=	UPPER('&1')
	AND		V.Profile_Option_Value				=	B.Name
	UNION
	SELECT
		R.Responsibility_Key,
		'BUDGETARY_CONTROL_OPTION'	Opt_Type,
		B.BC_Option_Name			Name,
		V.*
	FROM
		FND_RESPONSIBILITY_VL		R,
		FND_PROFILE_OPTIONS			O,
		FND_PROFILE_OPTION_VALUES	V,
		GL_BC_OPTIONS				B
	WHERE	O.Profile_Option_Name				=	'BUDGETARY_CONTROL_OPTION'
	AND		O.Application_ID					=	V.Application_ID
	AND		O.Profile_Option_ID					=	V.Profile_Option_ID
	AND		10003								=	V.Level_ID
	AND		R.Responsibility_ID					=	V.Level_Value
	AND		SUBSTR(R.Responsibility_Key,1,4)	=	UPPER('&1')
	AND		V.Profile_Option_Value				=	B.BC_Option_ID
	UNION
	SELECT
		R.Responsibility_Key,
		'PO_AUTOCREATE_DATE'		Opt_Type,
		V.Profile_Option_Value		Name,
		V.*
	FROM
		FND_RESPONSIBILITY_VL		R,
		FND_PROFILE_OPTIONS			O,
		FND_PROFILE_OPTION_VALUES	V
	WHERE	O.Profile_Option_Name				=	'PO_AUTOCREATE_DATE'
	AND		O.Application_ID					=	V.Application_ID
	AND		O.Profile_Option_ID					=	V.Profile_Option_ID
	AND		10003								=	V.Level_ID
	AND		R.Responsibility_ID					=	V.Level_Value
	AND		SUBSTR(R.Responsibility_Key,1,4)	=	UPPER('&1')
	UNION
	SELECT
		R.Responsibility_Key,
		'CURRENCY:NEGATIVE_FORMAT'	Opt_Type,
		B.Meaning					Name,
		V.*
	FROM
		FND_RESPONSIBILITY_VL		R,
		FND_PROFILE_OPTIONS			O,
		FND_PROFILE_OPTION_VALUES	V,
		FND_LOOKUPS					B
	WHERE	O.Profile_Option_Name				=	'CURRENCY:NEGATIVE_FORMAT'
	AND		O.Application_ID					=	V.Application_ID
	AND		O.Profile_Option_ID					=	V.Profile_Option_ID
	AND		10003								=	V.Level_ID
	AND		R.Responsibility_ID					=	V.Level_Value
	AND		SUBSTR(R.Responsibility_Key,1,4)	=	UPPER('&1')
	AND		'CURRENCY:NEGATIVE_FORMAT'			=	B.Lookup_Type
	AND		V.Profile_Option_Value				=	B.Lookup_Code
	ORDER BY 1, 2
	);
		
	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_FND_PROFILE;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
